const connection = require('../../sql');

class GeneratorTableService {
	async selectDbTableByImport({ tableName, tableComment, page, limit }) {
		try {
			const offset = (page - 1) * limit;
			let whereClause = '';
			const params = [];

			if (tableName) {
				whereClause += ' WHERE table_name LIKE ?';
				params.push(`%${tableName}%`);
			}
			if (tableComment) {
				whereClause += tableName ? ' AND table_comment LIKE ?' : ' WHERE table_comment LIKE ?';
				params.push(`%${tableComment}%`);
			}

			const countSql = `
        SELECT COUNT(*) as total 
        FROM generator_table 
        ${whereClause}
      `;
			const listSql = `
        SELECT 
          table_id AS tableId, 
          table_name AS tableName, 
          table_comment AS tableComment, 
          class_name AS className, 
          camel_class_name AS camelClassName, 
          tpl_category AS tplCategory, 
          package_name AS packageName, 
          module_name AS moduleName, 
          business_name AS businessName, 
          function_name AS functionName, 
          function_author AS functionAuthor, 
          type, 
          options, 
          parent_menu_id AS parentMenuId, 
          path, 
          path_api AS pathApi, 
          path_web AS pathWeb, 
          create_id AS createId, 
          update_id AS updateId, 
          create_time AS createTime, 
          update_time AS updateTime, 
          menu_init_type AS menuInitType, 
          btn_permission_type AS btnPermissionType, 
          has_import AS hasImport, 
          has_export AS hasExport, 
          generate_type AS generateType, 
          is_autofill AS isAutofill 
        FROM generator_table 
        ${whereClause}
        ORDER BY create_time ASC
        LIMIT ? OFFSET ?
      `;

			const [countResult] = await connection.execute(countSql, params);
			const [rows] = await connection.execute(listSql, [...params, String(limit), String(offset)]);

			return {
				current: page,
				limit,
				totalPage: Math.ceil(countResult[0].total / limit),
				total: countResult[0].total,
				rows: rows,
			};
		} catch (error) {
			throw error;
		}
	}

	async selectDbTableNotInImport({ tableName, tableComment, filterSys, page, limit }) {
		try {
			const offset = (page - 1) * limit;
			let whereClause = ' WHERE TABLE_SCHEMA = DATABASE()';
			const params = [];

			// 处理 NOT IN 子句
			const [existingTables] = await connection.execute('SELECT table_name FROM generator_table');
			const existingTableNames = existingTables.map((row) => row.table_name);
			if (existingTableNames.length > 0) {
				whereClause += ` AND TABLE_NAME NOT IN (${existingTableNames.map(() => '?').join(',')})`;
				params.push(...existingTableNames);
			}

			if (tableName) {
				whereClause += ' AND TABLE_NAME LIKE ?';
				params.push(`%${tableName}%`);
			}
			if (tableComment) {
				whereClause += ' AND TABLE_COMMENT LIKE ?';
				params.push(`%${tableComment}%`);
			}
			if (filterSys) {
				whereClause += " AND TABLE_NAME NOT LIKE 'sys_%'";
			}

			const countSql = `
            SELECT COUNT(*) as total 
            FROM information_schema.TABLES 
            ${whereClause}
          `;
			const listSql = `
            SELECT 
              '' AS tableId, 
              TABLE_NAME AS tableName, 
              TABLE_COMMENT AS tableComment, 
              '' AS className, 
              '' AS camelClassName, 
              '' AS tplCategory, 
              '' AS packageName, 
              '' AS moduleName, 
              '' AS businessName, 
              '' AS functionName, 
              '' AS functionAuthor, 
              '' AS type, 
              '' AS options, 
              '' AS parentMenuId, 
              '' AS path, 
              '' AS pathApi, 
              '' AS pathWeb, 
              '' AS createId, 
              '' AS updateId, 
              CREATE_TIME AS createTime, 
              UPDATE_TIME AS updateTime,  -- 注意：information_schema.TABLES 中没有 MODIFY_TIME，这里设为空
              '' AS menuInitType, 
              '' AS btnPermissionType, 
              '' AS hasImport, 
              '' AS hasExport, 
              '' AS generateType, 
              '' AS isAutofill 
            FROM information_schema.TABLES 
            ${whereClause}
            ORDER BY CREATE_TIME ASC
            LIMIT ? OFFSET ?
          `;

			const [countResult] = await connection.execute(countSql, params);
			const [rows] = await connection.execute(listSql, [...params, String(limit), String(offset)]);
			return {
				current: page,
				limit,
				totalPage: Math.ceil(countResult[0].total / limit),
				total: countResult[0].total,
				rows: rows,
			};
		} catch (error) {
			throw error;
		}
	}

	async importTables(tableNames) {
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();

			// 删除已存在的表记录
			await conn.execute('DELETE FROM generator_table_column WHERE table_id IN (SELECT table_id FROM generator_table WHERE table_name IN (?))', [
				tableNames,
			]);
			await conn.execute('DELETE FROM generator_table WHERE table_name IN (?)', [tableNames]);

			// 查询表信息
			const [tables] = await conn.execute(
				'SELECT TABLE_NAME AS tableName, TABLE_COMMENT AS tableComment FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (?)',
				[tableNames]
			);

			const importedTables = [];
			for (const table of tables) {
				const tableData = {
					table_name: table.tableName,
					table_comment: table.tableComment || table.tableName,
					class_name: this.toPascalCase(table.tableName),
					camel_class_name: this.toCamelCase(table.tableName),
					tpl_category: 'crud',
					package_name: 'com.example',
					module_name: table.tableName.replace(/_/g, ''),
					business_name: this.toCamelCase(table.tableName),
					function_name: table.tableComment || table.tableName,
					function_author: 'admin',
					type: '0',
					path: '/',
					path_api: '',
					path_web: '',
					menu_init_type: '1',
					btn_permission_type: '1',
					has_import: '1',
					has_export: '1',
					generate_type: 'all',
					is_autofill: '1',
					create_id: 1, // 假设用户 ID 为 1
					create_time: new Date(),
				};

				const [insertResult] = await conn.execute('INSERT INTO generator_table SET ?', tableData);
				const tableId = insertResult.insertId;
				importedTables.push(table.tableName);

				// 查询字段信息
				const [columns] = await conn.execute('SHOW FULL COLUMNS FROM ??', [table.tableName]);
				const columnData = columns.map((col, index) => ({
					table_id: tableId,
					column_name: col.Field,
					column_comment: col.Comment || col.Field,
					column_type: col.Type,
					java_type: this.mapJavaType(col.Type),
					ts_type: this.mapTsType(col.Type),
					java_field: this.toCamelCase(col.Field),
					up_camel_field: this.toPascalCase(col.Field),
					is_pk: col.Key === 'PRI' ? '1' : '0',
					is_increment: col.Extra === 'auto_increment' ? '1' : '0',
					is_required: col.Null === 'NO' && col.Default === null ? '1' : '0',
					is_insert: col.Key === 'PRI' ? '0' : '1',
					is_edit: col.Key === 'PRI' ? '0' : '1',
					is_list: '1',
					is_query: '1',
					query_type: 'EQ',
					html_type: this.mapHtmlType(col.Type),
					sort: index + 1,
					create_id: 1,
					create_time: new Date(),
				}));

				if (columnData.length > 0) {
					const values = columnData.map((col) => [
						col.table_id,
						col.column_name,
						col.column_comment,
						col.column_type,
						col.java_type,
						col.ts_type,
						col.java_field,
						col.up_camel_field,
						col.is_pk,
						col.is_increment,
						col.is_required,
						col.is_insert,
						col.is_edit,
						col.is_list,
						col.is_query,
						col.query_type,
						col.html_type,
						col.sort,
						col.create_id,
						col.create_time,
					]);
					await conn.execute(
						'INSERT INTO generator_table_column (table_id, column_name, column_comment, column_type, java_type, ts_type, java_field, up_camel_field, is_pk, is_increment, is_required, is_insert, is_edit, is_list, is_query, query_type, html_type, sort, create_id, create_time) VALUES ?',
						[values]
					);
				}
			}

			await conn.commit();
			return importedTables;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	// 辅助方法：映射 Java 类型
	mapJavaType(columnType) {
		const type = columnType.split('(')[0].toLowerCase();
		switch (type) {
			case 'bigint':
				return 'Long';
			case 'int':
			case 'integer':
				return 'Integer';
			case 'varchar':
			case 'text':
				return 'String';
			case 'datetime':
			case 'timestamp':
				return 'Date';
			default:
				return 'String';
		}
	}

	// 辅助方法：映射 TypeScript 类型
	mapTsType(columnType) {
		const type = columnType.split('(')[0].toLowerCase();
		switch (type) {
			case 'bigint':
			case 'int':
			case 'integer':
				return 'number';
			case 'varchar':
			case 'text':
				return 'string';
			case 'datetime':
			case 'timestamp':
				return 'string';
			default:
				return 'string';
		}
	}

	// 辅助方法：映射 HTML 类型
	mapHtmlType(columnType) {
		const type = columnType.split('(')[0].toLowerCase();
		switch (type) {
			case 'varchar':
				return 'input';
			case 'text':
				return 'textarea';
			case 'int':
			case 'integer':
			case 'bigint':
				return 'input-number';
			case 'datetime':
			case 'timestamp':
				return 'datetime';
			default:
				return 'input';
		}
	}

	// 辅助方法：转换为驼峰命名
	toCamelCase(str) {
		return str.replace(/_([a-z])/g, (_, char) => char.toUpperCase());
	}

	// 辅助方法：转换为首字母大写
	toPascalCase(str) {
		return str.replace(/(^|_)([a-z])/g, (_, __, char) => char.toUpperCase());
	}
}

module.exports = new GeneratorTableService();
